{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "mydf1 = pd.read_csv('mycl.csv')\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1.sample(frac=0.5,replace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf2 = mydf1.loc[mydf1['金额']>1500]l\n",
    "display(mydf2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf2.sample(n=2,weights=[0.1,0.1,0.2,0.1,0.2,0.2,0.1],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf2.sample(n=2,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {  \"编号\":[100001,100012,100003,100004],\n",
    "          \"日期\":pd.date_range('20211218', periods=4),\n",
    "          \"姓名\":[\"赵佳\",\"张可\",\"周远\",\"徐南\"],\n",
    "          \"性别\":['女','男','女','男'],\n",
    "          \"年龄\":[25,28,21,30],\n",
    "          \"工资\":[5869.32,7256.34,6895.89,7289.72]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data)\n",
    "mydf1['id'] = np.arange(1,mydf1.shape[0]+1)\n",
    "df = mydf1.set_index('id')\n",
    "df.to_csv('工资表.csv')\n",
    "display(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mydf1.describe()\n",
    "mydf1['工资'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mydf1.describe(percentiles=[0.125,0.25,0.5,0.75,0.875])\n",
    "display( mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "#mydf1.describe(include=[np.float64])\n",
    "\n",
    "#mydf1.describe(include=\"O\")\n",
    "mydf1.describe(exclude=[np.object_])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {  \"编号\":[1002.1,1002.2,3,'备注1'],\n",
    "          \"日期\":pd.date_range('20211218', periods=4),\n",
    "          \"姓名\":[\"赵可佳\",\"张可\",\"周可\",\"徐南\"],\n",
    "          \"性别\":['女','男','女','男'],\n",
    "          \"工龄\":[5,8,4,3],\n",
    "          \"工资\":[5869.32,7256.34,6895.89,7289.72]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data) \n",
    "print(mydf1.columns.tolist()[0].find('编号'))\n",
    "#mydf1.set_index(['姓名'])\n",
    "# my_index =  mydf1.loc[~mydf1['编号'].astype(str).str.contains('[\\u4e00-\\u9fa5]')]\n",
    "# #my_index = mydf1['编号'].astype(str)\n",
    "# my_index\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mydf1.iloc[:,0] = mydf1.iloc[:,0].replace('100001',0)\n",
    "# mydf1.columns = ['序号']+mydf1.columns[1:].tolist()\n",
    "# display(mydf1)\n",
    "\n",
    "# my_index =  mydf1.loc[mydf1['编号'].str.contains(r'\\d')]\n",
    "# print(mydf1['编号'].str.contains('备注'))\n",
    "\n",
    "# 使用正则表达式匹配包含中文字符的行\n",
    "condition = mydf1['编号'].str.contains('[\\u4e00-\\u9fa5]')\n",
    "\n",
    "# 删除满足条件的行\n",
    "mydf1 = mydf1.drop(mydf1[condition].index)\n",
    "mydf1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 创建一个包含字符串的DataFrame\n",
    "data = {'A': ['123', 'abc', '456', '中文', '789']}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 使用正则表达式匹配包含中文字符的行\n",
    "condition = df['A'].str.contains('[\\u4e00-\\u9fa5]')\n",
    "\n",
    "# 删除满足条件的行\n",
    "df = df.drop(df[condition].index)\n",
    "\n",
    "print(condition)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for row in mydf1.itertuples():\n",
    "#     print(row)\n",
    "for index,row in mydf1.iterrows():\n",
    "    print(index)\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1['姓名'] = mydf1['姓名'].replace('赵云',0); \n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mydf1.工龄.median()\n",
    "mydf1.median(numeric_only=True)\n",
    "display(mydf1.编号)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1['工资中位数'] = mydf1.loc[mydf1['性别'] == '男'].工资.median()\n",
    "mydf1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1.filter(like='可',axis=0).工龄.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1.filter(regex='可$',axis=0).工资.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydf1.loc[mydf1['工资'] > mydf1.filter(regex='可$',axis=0).工资.median()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mya = mydf1.filter(like='可',axis=0).工龄.median()\n",
    "myb = mydf1.filter(like='可$',axis=0).工资.median()\n",
    "mydf1.query(\"工资 >@myb  & 工龄 < @mya\")\n",
    "#mydf1.query(\"工龄 < @myx\")\n",
    "#mydf1.query(\"工资 >=7000 & 工龄 <@myx\")\n",
    "\n",
    "#mydf1.loc(mydf1['工资']>=7000 & mydf1['工龄'] < mydf1.filter(like='可',axis=0).工龄.median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>电器产品</th>\n",
       "      <th>业务员</th>\n",
       "      <th>时间</th>\n",
       "      <th>城市</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>541</td>\n",
       "      <td>125</td>\n",
       "      <td>67625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>674</td>\n",
       "      <td>125</td>\n",
       "      <td>84250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>720</td>\n",
       "      <td>125</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>641</td>\n",
       "      <td>125</td>\n",
       "      <td>80125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>721</td>\n",
       "      <td>125</td>\n",
       "      <td>90125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>夏季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>384</td>\n",
       "      <td>125</td>\n",
       "      <td>48000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>354</td>\n",
       "      <td>125</td>\n",
       "      <td>44250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>841</td>\n",
       "      <td>125</td>\n",
       "      <td>105125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  电器产品  业务员  时间  城市   数量   单价     销售额\n",
       "0  MP3   李可  春季  上海  541  125   67625\n",
       "1  MP3   李可  秋季  青岛  674  125   84250\n",
       "2  MP3   李亮  春季  上海  720  125   90000\n",
       "3  MP3   李亮  夏季  上海  641  125   80125\n",
       "4  MP3   张平  春季  上海  721  125   90125\n",
       "5  MP3   张平  夏季  青岛  384  125   48000\n",
       "6  MP3  周顺利  夏季  上海  354  125   44250\n",
       "7  MP3  周顺利  秋季  青岛  841  125  105125"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "mydf1  = pd.read_excel('myexcel1.xls',sheet_name=1)\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>数量</th>\n",
       "      <td>2.917286e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.646607e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>单价</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>销售额</th>\n",
       "      <td>3.646607e+06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.558259e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               数量   单价           销售额\n",
       "数量   2.917286e+04  0.0  3.646607e+06\n",
       "单价   0.000000e+00  0.0  0.000000e+00\n",
       "销售额  3.646607e+06  0.0  4.558259e+08"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print(mydf1['数量'].cov(mydf1['销售额']))\n",
    "#print(mydf1['单价'].cov(mydf1['销售额']))\n",
    "mydf1.cov(numeric_only=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(mydf1.销售额.var())\n",
    "#mydf1.loc[mydf1['城市']=='上海'].数量.var()\n",
    "mydf1.loc[(mydf1['时间']!='夏季')&(mydf1['城市'] == '青岛')].销售额.var()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "mydf1 = pd.read_csv('mycl.csv')\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mydf1.数量.std()\n",
    "#mydf1.loc[mydf1['数量'] < mydf1.数量.std()]\n",
    "mydf1.std(numeric_only=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>日期</th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>2021-12-18</td>\n",
       "      <td>赵佳</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>5869.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100012</td>\n",
       "      <td>2021-12-19</td>\n",
       "      <td>张可</td>\n",
       "      <td>男</td>\n",
       "      <td>28</td>\n",
       "      <td>7256.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>2021-12-20</td>\n",
       "      <td>周远</td>\n",
       "      <td>女</td>\n",
       "      <td>21</td>\n",
       "      <td>6895.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>2021-12-21</td>\n",
       "      <td>徐南</td>\n",
       "      <td>男</td>\n",
       "      <td>30</td>\n",
       "      <td>7289.72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       编号         日期  姓名 性别  年龄       工资\n",
       "0  100001 2021-12-18  赵佳  女  25  5869.32\n",
       "1  100012 2021-12-19  张可  男  28  7256.34\n",
       "2  100003 2021-12-20  周远  女  21  6895.89\n",
       "3  100004 2021-12-21  徐南  男  30  7289.72"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {  \"编号\":[100001,100012,100003,100004],\n",
    "          \"日期\":pd.date_range('20211218', periods=4),\n",
    "          \"姓名\":[\"赵佳\",\"张可\",\"周远\",\"徐南\"],\n",
    "          \"性别\":['女','男','女','男'],\n",
    "          \"年龄\":[25,28,21,30],\n",
    "          \"工资\":[5869.32,7256.34,6895.89,7289.72]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data) \n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>年龄</th>\n",
       "      <td>0.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>工资</th>\n",
       "      <td>0.8</td>\n",
       "      <td>0.8</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     编号   年龄   工资\n",
       "编号  1.0  0.6  0.8\n",
       "年龄  0.6  1.0  0.8\n",
       "工资  0.8  0.8  1.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mydf1['年龄'].corr(mydf1['工资'])\n",
    "#mydf1.corr(method='pearson',numeric_only=True)\n",
    "mydf1.corr(method='spearman',numeric_only=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
